CREATE                     procedure [dbo].[amsp_GetSearchableComponents]
  @SearchComponentList varchar(1000),
  @KeyID numeric
AS
BEGIN

  /*
  ** DESCRIPTION:
  ** ------------
  ** This sp returns the desired fields to be indexed for searching
  **
  ** This sp is designed to support the following component codes:
  **
  ** CM - URL Links from Content_Link
  **
  ** INPUTS:
  ** -------
  ** @SearchComponentList -> A comma separated list of Component Codes for
  **                         which to generate search records
  ** @KeyID               -> The primary key value of a specific record to be retrieved.  If
  **                         this is 0 then return all records for the specified component codes.
  **    
  **
  ** RESULT SET:
  ** -----------
  **   SearchKey            -> The Unique Search Key for the Record
  **   Title                -> Display Title in Search Results
  **   Body1                -> Main Content to be Indexed
  **   Body2                -> Optional Additional Content to Index
  **   InterestCategoryList -> Comma Separated List of IC's
  **   CustomField          -> Initially Set to Date, but may be Customized
  **
  ** NOTES:
  ** ------
  **   SearchKey has the following format:
  **     Position 1   -> 'Y' or 'N' indicating Members Only Content
  **     Position 2   -> ':'
  **     Position 3-4 -> 2 Character Component Code
  **     Position 5   -> ':'
  **     Position 6-? -> Component ID
  **
  ** PROGRAMMING CONSIDERATIONS:
  ** ---------------------------
  ** Dynamic SQL is intentionally not used.  Speed overrides elegance.
  ** Do not convert to dynamic SQL - It cannot be parsed until runtime.
  **
  ** HISTORY:
  ** --------
  ** 06/02/2001   N.Malhotra  Initial Version Created
  ** 06/15/2001   N.Malhotra  Added Date and CustomField2
  ** 09/04/2001   N.Malhotra  Added template to handle sites with more than one template
  ** 09/27/2001   N.Malhotra  Fixed a bug where the InterestCategroyList for the
  **                          first matchind content record was not being set properly.
  ** 10/25/2001   I.Kim       Fixed Newsletter to refer Sent_Email table.
  **                          However, you must configure Component_Ref table.
  **                          Also commented out Conference section (to be fixed by Nitin)
  ** 03/20/2002   I.Kim       For PR --> ISNULL(ArchiveFlag,'N') since ArchiveFlag can
  **                          contain null.
  ** 03/07/2003   E.Pfleckl   Changed the description for conferences to be NULL.
  **                          Added Calendar Events
  **                          Fixed several issues in Forums block and added
  **                          where clause to only include active forums
  ** 03/10/2003   E.Pfleckl   Changed Conference block to only return active conferences
  ** 08/25/2003   J.Stopple   Updated 'NL' section for new Newsletter Manager
  ** 10/14/2003   E.Tatsui    Modified to exclude admin websites.
  **                          Changed UserDefinedField1 to PublicationDate
  ** 05/12/2004   E.Tatsui    Added changes for conference and ecommerce.
  */

  DECLARE
    @ComponentCode                 char(2),
    @OldComponentCode              char(2),
    @ComponentID                   numeric,
    @OldComponentID                numeric,
    @InterestCategoryID            numeric,
    @InterestCategoryList          varchar(1000),
    @l_FirstRow                    int,
    @AdminWebsiteKey               uniqueidentifier,
    @NavMenuID                     numeric,
    @WebsiteKey                    uniqueidentifier

  /*
  ** Create a temp table that we'll fill with each type of component code
  ** specified in ComponentCodeList.
  **
  ** NOTE: CustomField is not used in the standard deployment.  This field is

  ** included in the results so that deployments requiring a custom field in

  ** their search (e.g. copyright date) need only modify this sp and not the CF
  ** code within each product utilizing this sp.
  */

  CREATE TABLE #temp (
    ComponentID            varchar(50),
    TemplatePath           varchar(255),
    ComponentCode          char(2),
    MembersOnlyFlag        char(1),
    Title                  varchar(255),
    Body1                  text,
    Body2                  text,
    InterestCategoryList   varchar(3000),
    CustomField2           varchar(255),
    NavMenuID              numeric,
    AllWebsiteKeys         varchar(1000),
    CustomID               varchar(50),
    IncludeInCrossSiteSearchFlag char(1) Default('Y')
  )

  SELECT @AdminWebsiteKey = Value
    FROM System_Variable
   WHERE Name = 'CMAdminWebsiteKey'

  /*
  ** For ComponentCode = 'CM' we need to pull Links
  */
  IF (CHARINDEX('CM', @SearchComponentList) > 0)

    IF @KeyID > 0
      INSERT INTO #temp (ComponentID, TemplatePath, ComponentCode, MembersOnlyFlag, Title, Body1, Body2, CustomField2, NavMenuID, AllWebsiteKeys, IncludeInCrossSiteSearchFlag, CustomID)
    SELECT     cl.ContentID, NULLIF (n.TemplatePath, '') AS Expr1, 'CM' AS Expr2, c.MembersOnlyFlag, cl.LinkName, c.Description, c.Keywords,
               CONVERT(VARCHAR(18), ISNULL(M.MicrositeID, '0')) + ',' + CONVERT(varchar(20), ISNULL(c.PublicationDate, CURRENT_TIMESTAMP), 102) AS Expr3,
               n.NavMenuID, n.WebsiteKey, w.IncludeInCrossSiteSearchFlag, cl.ContentLinkID
    FROM         Content_Link AS cl INNER JOIN
                          vCurrent_Content AS c ON cl.ContentID = c.ContentID INNER JOIN
                          Nav_Menu AS n ON c.NavMenuID = n.NavMenuID LEFT OUTER JOIN
                          Microsite AS M ON n.AncestorNavMenuID = M.HomeNavMenuID INNER JOIN
                          Website AS w ON n.WebsiteKey = w.WebsiteKey
    WHERE     (n.WebsiteKey <> @AdminWebsiteKey) AND (cl.ContentLinkID = @KeyID) AND (ISNULL(n.HideFlag, 'N') = 'N')
    ELSE
      INSERT INTO #temp (ComponentID, TemplatePath, ComponentCode, MembersOnlyFlag, Title, Body1, Body2, CustomField2, NavMenuID, AllWebsiteKeys, IncludeInCrossSiteSearchFlag, CustomID)
    SELECT     cl.ContentID, NULLIF (n.TemplatePath, '') AS Expr1, 'CM' AS Expr2, c.MembersOnlyFlag, cl.LinkName, c.Description, c.Keywords,
                          CONVERT(VARCHAR(18), ISNULL(M.MicrositeID, '0')) + ',' + CONVERT(varchar(20), ISNULL(c.PublicationDate, CURRENT_TIMESTAMP), 102) AS Expr3,
                          n.NavMenuID, n.WebsiteKey, w.IncludeInCrossSiteSearchFlag, cl.ContentLinkID
    FROM         Content_Link AS cl INNER JOIN
                          vCurrent_Content AS c ON cl.ContentID = c.ContentID INNER JOIN
                          Nav_Menu AS n ON c.NavMenuID = n.NavMenuID LEFT OUTER JOIN
                          Microsite AS M ON n.AncestorNavMenuID = M.HomeNavMenuID INNER JOIN
                          Website AS w ON n.WebsiteKey = w.WebsiteKey
    WHERE     (n.WebsiteKey <> @AdminWebsiteKey) AND (c.WorkflowStatusCode = 'P') AND (ISNULL(n.HideFlag, 'N') = 'N')

  /*
  ** Finally, after all rows have been added to the temp table, we need to
  ** generate the comma separated list of Interest Categories
  */
   DECLARE c_InterestCategory CURSOR FOR
    SELECT cic.ComponentCode,
           cic.InterestCategoryID,
           cic.ComponentID
      FROM Component_Interest_Category cic, #temp t
     WHERE cic.ComponentCode = t.ComponentCode
       AND cic.ComponentID = t.ComponentID
     ORDER BY cic.ComponentCode, cic.ComponentID

  OPEN c_InterestCategory
  
  FETCH NEXT FROM c_InterestCategory INTO
    @ComponentCode,
    @InterestCategoryID,
    @ComponentID

  SET @OldComponentCode = @ComponentCode
  SET @OldComponentID = @ComponentID
  SET @InterestCategoryList = @InterestCategoryID
  SET @l_FirstRow = 1

  WHILE (@@FETCH_STATUS=0) BEGIN
    IF ( @OldComponentCode != @ComponentCode or @OldComponentID != @ComponentID ) BEGIN
      UPDATE #temp
         SET InterestCategoryList = @InterestCategoryList
       WHERE ComponentCode = @OldComponentCode
         AND ComponentID = @OldComponentID

      SET @InterestCategoryList = @InterestCategoryID
    END
    ELSE BEGIN
      IF ( @l_FirstRow = 1 ) BEGIN
        SET @InterestCategoryList = @InterestCategoryList + ',' + convert(varchar(10), @InterestCategoryID)      
      END
      ELSE BEGIN
        SET @l_FirstRow = 0
      END
    END

    SET @OldComponentCode = @ComponentCode
    SET @OldComponentID = @ComponentID

    FETCH NEXT FROM c_InterestCategory INTO
      @ComponentCode,
      @InterestCategoryID,
      @ComponentID

  END

  UPDATE #temp
     SET InterestCategoryList = @InterestCategoryList
   WHERE ComponentCode = @OldComponentCode
     AND ComponentID = @OldComponentID

  CLOSE c_InterestCategory
  DEALLOCATE c_InterestCategory

  -- For each nav item, add Websites are marked to include in the search
  -- and only include those websites that has the setting on.
  UPDATE #temp
     SET AllWebsiteKeys = AllWebsiteKeys + ','
   WHERE AllWebsiteKeys IS NOT NULL

  DECLARE c_NavMenu CURSOR FOR
   SELECT DISTINCT b.NavMenuID, b.WebsiteKey
     FROM #temp a, Nav_Menu_Website b, Website c
    WHERE a.NavMenuID = b.NavMenuID
      AND a.AllWebsiteKeys NOT LIKE Convert(varchar(50),b.WebsiteKey) + '%'
      AND b.WebsiteKey = Convert(varchar(50),c.WebsiteKey)
      AND c.IncludeInCrossSiteSearchFlag = 'Y'

  OPEN c_NavMenu
  FETCH NEXT FROM c_NavMenu
   INTO @NavMenuID,
        @WebsiteKey
  WHILE @@FETCH_STATUS = 0 BEGIN
  
    UPDATE #temp
       SET AllWebsiteKeys = IsNull(AllWebsiteKeys,'') + Convert(varchar(50),@WebsiteKey) + ','
     WHERE NavMenuID = @NavMenuID

    FETCH NEXT FROM c_NavMenu
     INTO @NavMenuID,
          @WebsiteKey
  END
  CLOSE c_NavMenu
  DEALLOCATE c_NavMenu

  -- Remove extra ","
  UPDATE #temp
     SET AllWebsiteKeys = Left(AllWebsiteKeys,Len(AllWebsiteKeys)-1)
   WHERE AllWebsiteKeys IS NOT NULL

  UPDATE #temp
     SET ComponentID = ComponentID + '-' + CustomID
   WHERE CustomID IS NOT NULL

  SELECT IsNull(MembersOnlyFlag, 'N') + ':' + ComponentCode + ':' + ComponentID + ':' + IsNull(TemplatePath, 'NONE') as SearchKey,
         Title,
         Body1,
         Body2,
         InterestCategoryList,
         CASE WHEN AllWebsiteKeys IS NULL THEN ''
              ELSE 'WKEY:' + AllWebsiteKeys  + ','
         END + 'CSEARCH:' + IsNull(IncludeInCrossSiteSearchFlag,'Y') + ',' + CustomField2 AS CustomField2
    FROM #temp

END

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON

GO
GRANT EXECUTE ON  [dbo].[amsp_GetSearchableComponents] TO [IMIS]
GO